ALTER PROCEDURE [dbo].[mt_syn_user]--同步人员
@tablename varchar(50),
@tablelist varchar(2000),
@tablelist1 varchar(2000),
@tablelist2 varchar(2000),
@imp_admin varchar(50),
@imp_ip varchar(15),
@menu_serial varchar(20)=null,
@dep_serial int =null
as

SET ANSI_WARNINGS off
set nocount on

declare @n varchar(20)
declare @nall varchar(2000)
declare @err bigint
declare @dep_name nvarchar(200)

--begin transaction

	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#dt_user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table #dt_user
	create table #dt_user (
		[user_serial] [int] IDENTITY(1,1) NOT NULL,
    [user_no] nvarchar(20) COLLATE Chinese_PRC_CI_AS,
	  [user_lname] nvarchar(200) COLLATE Chinese_PRC_CI_AS,
    [user_fname] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
    [user_duty] nvarchar(20) COLLATE Chinese_PRC_CI_AS,
	  [user_depname] nvarchar(200) COLLATE Chinese_PRC_CI_AS,
    [user_sex] nvarchar(6) COLLATE Chinese_PRC_CI_AS,
    [user_nation] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
	  [user_xueli] nvarchar(20) COLLATE Chinese_PRC_CI_AS,
    [user_birthday] nvarchar(30) COLLATE Chinese_PRC_CI_AS,
    [user_workday] nvarchar(30) COLLATE Chinese_PRC_CI_AS,
	  [user_telephone] nvarchar(50) COLLATE Chinese_PRC_CI_AS,
    [user_id] nvarchar(50) COLLATE Chinese_PRC_CI_AS,
    [user_native] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
	  [user_address] nvarchar(200) COLLATE Chinese_PRC_CI_AS,
    [user_email] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
    [user_post] nvarchar(20) COLLATE Chinese_PRC_CI_AS,
	  [user_linkman] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
    [user_bz] nvarchar(500) COLLATE Chinese_PRC_CI_AS,
    [dep_no] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
	  [user_dep] nvarchar(200) COLLATE Chinese_PRC_CI_AS,
    t_rownum int,yich int default 0,
    yich_reason nvarchar(100) COLLATE Chinese_PRC_CI_AS,
    [cost_center] nvarchar(1000) COLLATE Chinese_PRC_CI_AS,
    [english_name] nvarchar(1000) COLLATE Chinese_PRC_CI_AS,
		[dep_no_add] nvarchar(100)  COLLATE Chinese_PRC_CI_AS)



	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#insert_user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table #insert_user
	create table #insert_user (
	[xh] [int] IDENTITY(1,1) NOT NULL,[user_serial] [int] ,[user_no] nvarchar(20) COLLATE Chinese_PRC_CI_AS,
	[user_lname] nvarchar(200) COLLATE Chinese_PRC_CI_AS,[user_fname] nvarchar(100) COLLATE Chinese_PRC_CI_AS,			[user_duty] nvarchar(20) COLLATE Chinese_PRC_CI_AS,
	[user_depname] nvarchar(200) COLLATE Chinese_PRC_CI_AS,[user_sex] nvarchar(6) COLLATE Chinese_PRC_CI_AS,[user_nation] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
	[user_xueli] nvarchar(20) COLLATE Chinese_PRC_CI_AS,[user_birthday] nvarchar(30) COLLATE Chinese_PRC_CI_AS,[user_workday] nvarchar(30) COLLATE Chinese_PRC_CI_AS,
	[user_telephone] nvarchar(50) COLLATE Chinese_PRC_CI_AS,[user_id] nvarchar(50) COLLATE Chinese_PRC_CI_AS,[user_native] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
	[user_address] nvarchar(200) COLLATE Chinese_PRC_CI_AS,[user_email] nvarchar(100) COLLATE Chinese_PRC_CI_AS,[user_post] nvarchar(20) COLLATE Chinese_PRC_CI_AS,
	[user_linkman] nvarchar(100) COLLATE Chinese_PRC_CI_AS,[user_bz] nvarchar(500) COLLATE Chinese_PRC_CI_AS,
  [dep_no] nvarchar(100) COLLATE Chinese_PRC_CI_AS,
	[user_dep] nvarchar(200) COLLATE Chinese_PRC_CI_AS,t_rownum int,yich int default 0,
  yich_reason nvarchar(100) COLLATE Chinese_PRC_CI_AS,
  [cost_center] nvarchar(1000) COLLATE Chinese_PRC_CI_AS,
  [english_name] nvarchar(1000) COLLATE Chinese_PRC_CI_AS,[dep_no_add] nvarchar(100) COLLATE Chinese_PRC_CI_AS)


	exec('update '+@tablename+' set user_workday=null   where user_workday=''''')
	exec('update '+@tablename+' set user_birthday=null   where user_birthday=''''')

	exec ('delete '+@tablename+' where isnull(user_no,'''')='''' and isnull(user_lname,'''')='''' and  isnull(user_fname,'''')='''' and isnull(user_duty,'''')=''''  and isnull(user_depname,'''')='''' and isnull(user_sex,'''')='''' and isnull(user_xueli,'''')='''' and isnull(user_birthday,'''')='''' and isnull(user_workday,'''')='''' and isnull(user_telephone,'''')='''' and  isnull(user_id,'''')='''' and isnull(user_native,'''')='''' and isnull(user_address,'''')='''' and isnull(user_email,'''')='''' and isnull(user_post,'''')='''' and isnull(user_linkman,'''')='''' and isnull(user_bz,'''')='''' and isnull(user_nation,'''')=''''
	and isnull(cost_center,'''')='''' and isnull(english_name,'''')='''''  )

--*********************
--批量插入人员
 exec('insert into #dt_user ([user_no],[user_lname],[user_fname],[user_duty],[user_depname],
 [user_sex],[user_nation],[user_xueli],[user_birthday],[user_workday],[user_telephone],
 [user_id],[user_native],[user_address],[user_email],[user_post],[user_linkman],[user_bz],t_rownum,cost_center,english_name,dep_no_add)
 select left(user_no,20),left(user_lname,50),left(user_fname,50),left(user_duty,20),left(user_depname,50),left(user_sex,8),left(user_nation,20),
 left(user_xueli,20),[user_birthday],[user_workday],left(user_telephone,30),left(user_id,30),left(user_native,20),left(user_address,50),
 left(user_email,50),left(user_post,20),left(user_linkman,50),left(user_bz,200),rownum,cost_center,english_name,dep_no_add from '+@tablename )

  -----*****处理异常数据*****-------------------
 if exists(select 1 from #dt_user where isnull(user_no,'')=''  or isnull(user_lname,'')='')
begin
	update #dt_user set yich=1,yich_reason='人员工号或者姓名不能为空' where isnull(user_no,'')=''  or isnull(user_lname,'')=''
end

if exists(select 1 from #dt_user group by user_no having count(user_serial)>1)
begin
	update #dt_user set yich=1,yich_reason='人员工号不能重复！' from #dt_user a
	inner join (select user_no from #dt_user  group by user_no having count(user_serial)>1) b
	on a.user_no=b.user_no
end

if exists(select 1 from #dt_user where yich=1)
begin
	exec('update '+@tablename +' set yich=b.yich,yich_reason=b.yich_reason from '+@tablename +
	 ' a inner join #dt_user b on a.rownum=b.t_rownum')

	exec('update '+@tablename +' set  mt_state=3 where yich=1 ')
end

 -----*****处理异常数据结束*****-------------------

--处理部门为空的记录，如果为空直接更新为根部门名称
select @dep_name=dep_name from dt_dep where dep_serial=isnull(@dep_serial,10000)
exec('update '+@tablename+' set user_depname='+''''+@dep_name+''''+' where isnull(user_depname,'''')='''' and yich=0')

update #dt_user set user_depname=@dep_name where user_depname is null or user_depname=''

--批量创建没有的部门到一级目录
select @n=module_dep from wt_module where module_id='0002'
--取需要增加的条数更新最大部门序号
exec('update wt_module set Module_dep=isnull(Module_dep,0)+isnull
((select count(distinct(user_depname)) from '+@tablename+
' a left join dt_dep b on a.user_depname=b.dep_name where b.dep_serial is null and a.yich=0),0)
where module_id=''0002''')

exec('insert into dt_dep(dep_serial,dep_parent,dep_order,dep_name,dep_no,dep_rule)
select dep_serial='+@n+'+(select COUNT(1) from(select user_depname from '
+@tablename+' a left join dt_dep b on a.user_depname=b.dep_name
where b.dep_serial is null  and a.yich=0 group by user_depname) aa
where user_depname<a.user_depname)+1,dep_parent=10000,
dep_order=(select count(1) from dt_dep where dep_parent=''10000'')+
(select COUNT(1) from (select user_depname from '+@tablename+
' a left join dt_dep b on a.user_depname=b.dep_name where b.dep_serial is null and a.yich=0 group by user_depname)
aa where user_depname<a.user_depname)+1,dep_name=a.user_depname,dep_no=cast(''001'' as varchar)
+right(cast(power(10,2) as varchar)+Convert(varchar,dbo.Sys_Convert((select count(1) from dt_dep where dep_parent=''10000'')+(select COUNT(1) from(select user_depname from '
+@tablename+' a left join dt_dep b on a.user_depname=b.dep_name where b.dep_serial is null   and a.yich=0
group by user_depname) aa where user_depname<a.user_depname)+1)),2),dep_rule=0
from (select user_depname from '+@tablename+' a left join dt_dep b
on a.user_depname=b.dep_name where b.dep_serial is null  and a.yich=0 group by user_depname) a')

--exec('insert into dt_ac_dep_user(dep_serial,dep_lname,user_serial,sj,module_id,per_lx)
--select b.dep_serial,b.dep_name,a.user_serial,GETDATE(),''0035'',0 from dt_ac_dep_user a left join
--(select a.user_serial,b.dep_serial,b.dep_name from dt_user a left join (select a.dep_serial,a.dep_name  from dt_ac_dep a left join (select acdep_serial from xf_dep_ac_time where ac_type=''0000000000000001'' group by acdep_serial ) b on a.dep_serial=b.acdep_serial where a.module_id =''0035'')b on 1=1) b on a.user_serial=b.user_serial where b.user_serial is null')



--更新档案的日志
 if (@menu_serial='M00023')  --写操作日志到wt_user_log
begin

	insert into WT_USER_LOG(lx,log_state,log_type,module_id,log_sj,log_bz,log_ip,gly_no)
	select 1,0,8,'0002',GETDATE(),'0='+a.user_no+'='+a.user_lname+'='+a.user_depname,@imp_ip,@imp_admin
	from #dt_user  a inner join dt_user b on a.user_no=b.user_no
	--exec('insert into WT_USER_LOG(lx,log_state,log_type,module_id,log_sj,log_bz,log_ip,gly_no)
	--select 1,0,8,''0002'',GETDATE(),''0=''+a.user_no+''=''+a.user_lname+''=''+a.user_depname,@imp_ip,@imp_admin
	--from'+@tablename+' a inner join dt_user b on a.user_no=b.user_no ')
 end



--*********************************
 --部门有调动的人员需要处理消费的金额  --改用循环
create table #temp_user(
xh int identity(1,1) primary key,
user_serial bigint,
user_dep int,
depname_a1 nvarchar(50)  COLLATE Chinese_PRC_CI_AS,
user_depname nvarchar(50)  COLLATE Chinese_PRC_CI_AS
)

declare @count_jl int
declare @jl_xh int
declare @user_serialy bigint
declare @user_depy int
declare @user_depx int
declare @depname_a1 nvarchar(50)
declare @user_depname nvarchar(50)

	insert into #temp_user select a.user_serial,b.user_dep,a.user_depname ,b.user_depname from #dt_user a
	inner join dt_user b on a.user_no=b.user_no and a.user_depname<>b.user_depname

set @count_jl=@@ROWCOUNT
set @jl_xh=1

while(@jl_xh<=@count_jl)
begin
select @user_serialy=user_serial,@user_depy=user_dep,@depname_a1=depname_a1,@user_depname=user_depname
from #temp_user where xh=@jl_xh

--获取此人员要新调入的部门序号
	select @user_depx=dep_serial from dt_dep where dep_name=@depname_a1
	exec real_user_remove '',@user_serialy,@user_depy,@user_depx,'admin','127.0.0.2'  ----人员企业,调动人员,调动前部门,调动后部门

set @jl_xh=@jl_xh+1
end

--********************************
------------------------------------
--批量更新人员，根据工号更新之前dt_user表存在的档案信息
exec('update dt_user set '+@tablelist2+',user_sj=getdate() from '+@tablename+
' a inner join dt_user b on a.user_no=b.user_no inner join dt_dep c on a.user_depname=c.dep_name')


select @n=module_user from wt_module where module_id='0002'
--取需要增加的条数更新最大人员序号
exec('update wt_module set Module_user=isnull(Module_user,0)+
(select count(1) from '+@tablename+' a left join dt_user b on a.user_no=b.user_no where
b.user_serial is null) where module_id=''0002''')


exec('insert into #insert_user('+@tablelist+')
select  a.user_serial as user_serial,'+@tablelist1+'from #dt_user a left join
 dt_user c on a.user_no=c.user_no left join  (select b.dep_serial,b.dep_name,b.dep_no,b.dep_no_add
 from (select min(dep_serial) dep_serial,dep_no_add from dt_dep group by dep_no_add) a,
 dt_dep b where a.dep_serial=b.dep_serial)b on a.dep_no_add=b.dep_no_add  where c.user_serial is null and a.yich=0')

 --20181016 lrh 调整人员档案导入时生成档案日志的处理位置beg
 if (@menu_serial='M00023')
begin

	insert into WT_USER_LOG(lx,log_state,log_type,module_id,log_sj,log_bz,log_ip,gly_no)
	--select 0,0,8,'0002',GETDATE(),cast(a.user_serial+@n as varchar(25))+'='+a.user_no+'='
	--+a.user_lname+'='+a.user_depname,@imp_ip,@imp_admin from #insert_user a

	select 0,0,8,'0002',GETDATE(),cast(a.xh+@n as varchar(25))+'='+a.user_no+'='
	+a.user_lname+'='+a.user_depname,@imp_ip,@imp_admin from #insert_user a left join dt_user b
	on a.user_no=b.user_no where b.user_serial is null

end

  --20181016 lrh 调整人员档案导入时生成档案日志的处理位置end
  --20190505 lqj 插入之前，除去档案名称中的空格.
 update #insert_user set user_lname=REPLACE( REPLACE(user_lname, CHAR(13) , ''), CHAR(10), '');
 exec('insert into dt_user('+@tablelist+',user_type,user_level,user_finger,user_photo,kq_tiaoxiu,user_sj)
select '+@n+'+a.xh as user_serial,'+@tablelist1+',0 as user_type,0 as user_level,''0000000000''
 as user_finger,0 as user_photo,0 as kq_tiaoxiu,getdate() from #insert_user a left join dt_dep b on a.user_dep=b.dep_serial')

 /*
if (@menu_serial='M00023')
begin

	insert into WT_USER_LOG(lx,log_state,log_type,module_id,log_sj,log_bz,log_ip,gly_no)
	--select 0,0,8,'0002',GETDATE(),cast(a.user_serial+@n as varchar(25))+'='+a.user_no+'='
	--+a.user_lname+'='+a.user_depname,@imp_ip,@imp_admin from #insert_user a

	select 0,0,8,'0002',GETDATE(),cast(a.xh+@n as varchar(25))+'='+a.user_no+'='
	+a.user_lname+'='+a.user_depname,@imp_ip,@imp_admin from #insert_user a left join dt_user b
	on a.user_no=b.user_no where b.user_serial is null

end
 */

--处理部门名称为空的情况
if exists(select 1 from dt_dep where dep_name is null)
begin
	delete from dt_dep where dep_name is null
	update dt_user set dep_no='001',user_dep=10000,
		user_depname=(select dep_name from dt_dep where dep_serial=10000)
	where user_dep is null
end

--批量更新表状态
exec('update '+@tablename+' set mt_state=2 where yich=0')
--插入默认账户

exec('declare @ac_pass varchar(50),@ac_limit int,@ac_unit int,@ac_jssj datetime;
select @ac_pass=Ac_pass,@ac_limit=isnull(ac_limit,0),@ac_unit=ac_unit,@ac_jssj=Ac_jssj
from dt_ac_type where ac_bh=''0000000000000001'';if (@ac_limit<>0) begin if(@ac_unit=0)select
@ac_jssj=DateAdd(year,@ac_limit,getdate());else if(@ac_unit=1)select
@ac_jssj=DateAdd(month,@ac_limit,getdate());else if(@ac_unit=2)select
@ac_jssj=DateAdd(day,@ac_limit,getdate());else if(@ac_unit=3)select
@ac_jssj=DateAdd(hour,@ac_limit,getdate());else if(@ac_unit=4)select
@ac_jssj=DateAdd(minute,@ac_limit,getdate())	else if(@ac_unit=5)
select @ac_jssj=DateAdd(second,@ac_limit,getdate()) end;insert into
dt_ac_user(ac_type,user_serial,ac_order,ac_money,ac_addm,ac_addo,ac_subm,ac_subo,ac_regm,ac_make,
ac_pass,ac_kssj,ac_jssj,ac_state,sj,gly_no,ac_overdraw,Ac_eachm,Ac_eacho)select ''0000000000000001'',
b.user_serial,0,0,0,0,0,0,0,0,@ac_pass,getdate(),@ac_jssj,0,getdate(),'''+
@imp_admin+''',0,0,0 from '+@tablename+' a inner join dt_user b on a.user_no=b.user_no
left join dt_ac_user c on b.user_serial=c.user_serial where c.user_serial is null and a.yich=0')



 --更新人员账户状态
update dt_user set user_ac=1  from dt_user a,dt_ac_user b where a.user_serial=b.user_serial and b.ac_no is null
 set @err=@err+@@error
 --更新为空的账户
update dt_ac_user set ac_no=
CONVERT(varchar(100), GETDATE(), 12)+cast(right('0000000000'+cast(xh as varchar(10)),10) as varchar(10))
where ac_no is null


exec('insert dt_ac_link(user_serial,ac_money,ac_addm,ac_addo,ac_subm,ac_subo,ac_regm,ac_make,ac_eachm,
ac_eacho,ac_blockm,ac_blocks,jl_count,ac_state,sj,gly_no)select b.user_serial,0,0,0,0,0,0,0,0,0,0,0,0,0,
getdate(),'''+@imp_admin+''' from '+@tablename+' a inner join dt_user b on a.user_no=b.user_no
left join dt_ac_link c on b.user_serial=c.user_serial where c.user_serial is null and a.yich=0')


insert into dt_ac_dep_user(dep_serial,dep_lname,user_serial,sj,module_id,per_lx)
select e.dep_serial,e.dep_name,e.user_serial,GETDATE(),'0035',0 from
(select b.dep_serial,b.dep_name,d.user_serial from  dt_ac_dep b inner join
(select distinct acdep_serial acdep_serial from xf_dep_ac_time where ac_type='0000000000000001')a
on a.acdep_serial=b.dep_serial,dt_user d ) e
left join dt_ac_dep_user f
 on  e.user_serial=f.user_serial where f.user_serial is null

--档案增量更新日志，全量
insert wt_public(is_all,lx,log_type,log_sj,log_ip,gly_no)values(1,1,1,getdate(),@imp_ip,@imp_admin)
	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#dt_user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table #dt_user

if exists(select 1 from #dt_user where yich=1)
begin
	select 1
end
else
begin
	select 0
end

--commit transaction
return
